<?php

namespace backend\models;

use Yii;
use yii\base\Model;
use yii\data\ActiveDataProvider;
use backend\models\PayTrade;
use yii\base\Object;

/**
 * PayTradeSearch represents the model behind the search form about `backend\models\PayTrade`.
 */
class PayTradeSearch extends PayTrade
{
	public $username;
	public $start_time;
	public $end_time;
	public $operator_id;
	public $operator_address;
	public $operator_self_id;
	public $user_area;
	
    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['id', 'user_id', 'number', 'price_state',  'modify_time', 'admin_uid', 'trade_state', 'audit_state', 'audit_uid', 'audit_time'], 'integer'],
            [['trade_no', 'address', 'message', 'price_m_memo', 'content','create_time'], 'safe'],
            [['price', 'price_modify', 'price_freight', 'price_total'], 'number'],
            [['username'],'string','max'=>20,],
            [['operator_address'],'string','max'=>20],
        		[['user_area'],'string'],
            [['start_time','end_time','operator_id','operator_self_id'],'integer'],
        		[['year'],'integer','max'=>date('Y')],
        ];
    }

    /**
     * @inheritdoc
     */
    public function scenarios()
    {
        // bypass scenarios() implementation in the parent class
        return Model::scenarios();
    }

    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function search($params,$dunning=false)
    {
        $this->load($params);
        $user_type = Yii::$app->user->identity->user_type;
        $user_id=Yii::$app->user->identity->user_id;
        $condition="pay_trade.trade_class=1 ";
        //如果是非管理员，则根据地址过滤
        if ($user_type != 1)
        {//非后台管理员
        	    if(intval($user_id)>0){
        	        $ifxgt=OperatorRelation::getifXat();//香港 澳门 台湾   直接in('台湾省','香港特别行政区','澳门特别行政区')
        	        $ifhascity=OperatorRelation::ifhascity();
        	        $ifhaszxsCountry=OperatorRelation::ifhaszxsCountry();
        	        $ifhascountry=OperatorRelation::ifhascountry();
        	        $ifhasprovince=OperatorRelation::ifhasprovince();
        	        if($ifhasprovince){
        	            $provinceStr=OperatorRelation::getProvinceNameById();
        	            $condition.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$provinceStr})";
        	        } 
        	        if($ifhascity){
        	            $cityStr=OperatorRelation::getCityNameByIdNprovincename();
        	            if($ifhasprovince){
        	                $condition.=" or SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
        	            }else{
        	                $condition.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
        	            }
        	         }
        	        if($ifhaszxsCountry||$ifhascountry){
        	            if($ifhasprovince||$ifhascity){
        	                $a="or";
        	            }else{
        	                $a="and";
        	            }
        	            $countryStr=OperatorRelation::getcountryNameById();
        	            if($ifhaszxsCountry){//有直辖市 也有区县 合并条件
        	                if($ifhascountry){
        	                    //$condition.=$a."  (SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$countryStr}) or SUBSTR(SUBSTRING_INDEX(address,' ',3),CHAR_LENGTH( SUBSTRING_INDEX(address,' ',2))+1) in ({$countryStr}))";
        	                    $condition.=$a."  (SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) or SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr}))";
        	                }else{
        	                    //$condition.=$a."  SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$countryStr}) ";
        	                    $condition.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) ";
        	                }
        	            }else{
        	                if($ifhascountry){
        	                  //  $condition.=$a."  SUBSTR(SUBSTRING_INDEX(address,' ',3),CHAR_LENGTH( SUBSTRING_INDEX(address,' ',2))+1) in ({$countryStr})";
        	                    $condition.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr})";
        	                } 
        	            }
        	        }
        	    }
        	 
        	//催款搜索
        	if ($dunning){
        	    $condition.=" and price_state=11 and log_pay.state=2";
        	}
        }else{ //管理员
            //催款搜索
            if ($dunning){
                $condition.=" and price_state=11 and log_pay.state=2";
            }
        }
        $query = PayTrade::find()->joinWith(['userInfo','logPayInfoFirst'])->with('payLogistics.iteminfo')->where($condition);
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'sort' => ['defaultOrder' => ['id'=>SORT_DESC]]
        ]);

        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }

          $query->andFilterWhere([
            'pay_trade.id' => $this->id,
            'user_id' => $this->user_id,
            'number' => $this->number,
            'price' => $this->price,
            'price_modify' => $this->price_modify,
            'price_freight' => $this->price_freight,
            'price_total' => $this->price_total,
            'price_state' => $this->price_state,
          //  'create_time' => $this->create_time,
            'modify_time' => $this->modify_time,
            'admin_uid' => $this->admin_uid,
            'trade_state' => $this->trade_state,
            'audit_state' => $this->audit_state,
            'audit_uid' => $this->audit_uid,
            'audit_time' => $this->audit_time,
        ]);

        $query->andFilterWhere(['like', 'pay_trade.trade_no', $this->trade_no])
            ->andFilterWhere(['like', 'address', $this->address])
            ->andFilterWhere(['like', 'message', $this->message])
            ->andFilterWhere(['like', 'price_m_memo', $this->price_m_memo])
            ->andFilterWhere(['like', 'content', $this->content])
        	->andFilterWhere(['like', 'user_info.username', $this->username])
        	->andFilterWhere(['like', 'FROM_UNIXTIME(create_time, "%Y-%m-%d")', $this->create_time])
        ;  
        		
        return $dataProvider;
    }
    /**
     * 众筹订单
     * @param unknown $params
     * @param string $dunning
     * @return \yii\data\ActiveDataProvider
     */
    public function crowd_search($params,$dunning=false)
    {
        $this->load($params);
        $user_type = Yii::$app->user->identity->user_type;
        $user_id=Yii::$app->user->identity->user_id;
        $condition="pay_trade.trade_class=2 ";
        //如果是非管理员，则根据地址过滤
        if ($user_type != 1 )
        {//非后台管理员
                if(intval($user_id)>0){
                    $ifxgt=OperatorRelation::getifXat();//香港 澳门 台湾   直接in('台湾省','香港特别行政区','澳门特别行政区')
                    $ifhascity=OperatorRelation::ifhascity();
                    $ifhaszxsCountry=OperatorRelation::ifhaszxsCountry();
                    $ifhascountry=OperatorRelation::ifhascountry();
                    $ifhasprovince=OperatorRelation::ifhasprovince();
                    if($ifhasprovince){
                        $provinceStr=OperatorRelation::getProvinceNameById();
                        $condition.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$provinceStr})";
                    }
                    if($ifhascity){
                        $cityStr=OperatorRelation::getCityNameByIdNprovincename();
                        if($ifhasprovince){
                            $condition.=" or SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                        }else{
                            $condition.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                        }
                    }
                    if($ifhaszxsCountry||$ifhascountry){
                        if($ifhasprovince||$ifhascity){
                            $a="or";
                        }else{
                            $a="and";
                        }
                        $countryStr=OperatorRelation::getcountryNameById();
                        if($ifhaszxsCountry){//有直辖市 也有区县 合并条件
                            if($ifhascountry){
                                //$condition.=$a."  (SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$countryStr}) or SUBSTR(SUBSTRING_INDEX(address,' ',3),CHAR_LENGTH( SUBSTRING_INDEX(address,' ',2))+1) in ({$countryStr}))";
                                $condition.=$a."  (SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) or SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr}))";
                            }else{
                                //$condition.=$a."  SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$countryStr}) ";
                                $condition.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) ";
                            }
                        }else{
                            if($ifhascountry){
                                //  $condition.=$a."  SUBSTR(SUBSTRING_INDEX(address,' ',3),CHAR_LENGTH( SUBSTRING_INDEX(address,' ',2))+1) in ({$countryStr})";
                                $condition.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr})";
                            }
                        }
                    }
                }
            
            //催款搜索
            if ($dunning){
                $condition.=" and price_state=11 and log_pay.state=2";
            }
        }else{ //管理员
            //催款搜索
            if ($dunning){
                $condition.=" and price_state=11 and log_pay.state=2";
            }
        }
        $query = PayTrade::find()->joinWith(['userInfo','logPayInfoFirst'])->with('payLogistics.iteminfo')->where($condition);
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);
        
        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }
        
        $query->andFilterWhere([
            'pay_trade.id' => $this->id,
            'user_id' => $this->user_id,
            'number' => $this->number,
            'price' => $this->price,
            'price_modify' => $this->price_modify,
            'price_freight' => $this->price_freight,
            'price_total' => $this->price_total,
            'price_state' => $this->price_state,
            //  'create_time' => $this->create_time,
            'modify_time' => $this->modify_time,
            'admin_uid' => $this->admin_uid,
            'trade_state' => $this->trade_state,
            'audit_state' => $this->audit_state,
            'audit_uid' => $this->audit_uid,
            'audit_time' => $this->audit_time,
        ]);
        
        $query->andFilterWhere(['like', 'pay_trade.trade_no', $this->trade_no])
        ->andFilterWhere(['like', 'address', $this->address])
        ->andFilterWhere(['like', 'message', $this->message])
        ->andFilterWhere(['like', 'price_m_memo', $this->price_m_memo])
        ->andFilterWhere(['like', 'content', $this->content])
        ->andFilterWhere(['like', 'user_info.username', $this->username])
        ->andFilterWhere(['like', 'FROM_UNIXTIME(create_time, "%Y-%m-%d")', $this->create_time])
        ;
        
        return $dataProvider;
    }
    /*报表的搜索 */
    public function reportSearch($params)
    {   	
        $this->load($params);
        //根据运营商地区来   
        $user_type = Yii::$app->user->identity->user_type;
        $user_id=Yii::$app->user->identity->user_id;
        $condition=" pay_trade.trade_class=1   and pay_trade.price_state in (1,11) ";
        $where_sum = "      p.trade_class=1 and p.price_state in (1,11)  ";//计和
        //运营商 看自己的订单
        if(intval($user_type)>1){
            $ifxgt=OperatorRelation::getifXat();//香港 澳门 台湾   直接in('台湾省','香港特别行政区','澳门特别行政区')
            $ifhascity=OperatorRelation::ifhascity();
            $ifhaszxsCountry=OperatorRelation::ifhaszxsCountry();
            $ifhascountry=OperatorRelation::ifhascountry();
            $ifhasprovince=OperatorRelation::ifhasprovince();
            if($ifhasprovince){
                $provinceStr=OperatorRelation::getProvinceNameById();
                $condition.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$provinceStr})";
                $where_sum.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$provinceStr})";
            }
            if($ifhascity){
                $cityStr=OperatorRelation::getCityNameByIdNprovincename();
                if($ifhasprovince){
                    $condition.=" or SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                    $where_sum.=" or SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                }else{
                    $condition.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                    $where_sum.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                }
            }
            if($ifhaszxsCountry||$ifhascountry){
                if($ifhasprovince||$ifhascity){
                    $a="or";
                }else{
                    $a="and";
                }
                $countryStr=OperatorRelation::getcountryNameById();
                if($ifhaszxsCountry){//有直辖市 也有区县 合并条件
                    if($ifhascountry){
                        $condition.=$a."  (SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) or SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr}))";
                        $where_sum.=$a."  (SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) or SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr}))";
                    }else{
                        $condition.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) ";
                        $where_sum.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) ";
                    }
                }else{
                    if($ifhascountry){
                        $condition.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr})";
                        $where_sum.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr})";
                    }
                }
                
            }
        //管理员 查看所有的  也可以查看某个运营商的    
        } 
            //operator_address 区域    operator_self_id 自己的
            //查看运营商自己订单
            if($this->operator_self_id){
                $condition.=" and user_id=".$this->operator_self_id." ";
                $where_sum.=" and user_id=".$this->operator_self_id;
            }
            if($this->operator_address){
                $operation_id=intval($this->operator_address);
                if($operation_id>0){
                    $ifxgt=OperatorRelation::getifXatByOid($operation_id);//香港 澳门 台湾   直接in('台湾省','香港特别行政区','澳门特别行政区')
                    $ifhascity=OperatorRelation::ifhascityByOid($operation_id);
                    $ifhaszxsCountry=OperatorRelation::ifhaszxsCountryByOid($operation_id);
                    $ifhascountry=OperatorRelation::ifhascountryByOid($operation_id);
                    $ifhasprovince=OperatorRelation::ifhasprovinceByOid($operation_id);
                    if($ifhasprovince){
                        $provinceStr=OperatorRelation::getProvinceNameByOId($operation_id);
                        $condition.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$provinceStr})";
                        $where_sum.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$provinceStr})";
                    }
                    if($ifhascity){
                        $cityStr=OperatorRelation::getCityNameByOIdNprovincename($operation_id);
                        if($ifhasprovince){
                            $condition.=" or SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                            $where_sum.=" or SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                            
                        }else{
                            $condition.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                            $where_sum.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                            
                        }
                    }
                    if($ifhaszxsCountry||$ifhascountry){
                        if($ifhasprovince||$ifhascity){
                            $a=" or";
                        }else{
                            $a=" and";
                        }
                        $countryStr=OperatorRelation::getcountryNameByOId($operation_id);
                        if($ifhaszxsCountry){//有直辖市 也有区县 合并条件
                            if($ifhascountry){
                                $condition.=$a."  (SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) or SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr}))";
                                $where_sum.=$a."  (SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) or SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr}))";
                            }else{
                                $condition.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) ";
                                $where_sum.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) ";
                            }
                        }else{
                            if($ifhascountry){
                                $condition.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr})";
                                $where_sum.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr})";
                            }
                        }
                    }
                }
            }
         $query = PayTrade::find()->where($condition)->joinWith(['logPayInfo'])->joinWith(['userInfo'])->groupBy("pay_trade.id");
        //$query=LogPay::find()->where($condition)->joinWith(['payTradeInfo'])->joinWith(['userInfo']);
        $arr = array();
    	if($this->start_time){
    		$query->andFilterWhere(['>=', 'create_time', strtotime($this->start_time)]);
    		$where_sum .= " AND p.create_time >= :start_time";//计和
    		$arr[':start_time'] = strtotime($this->start_time);
    	}
    	 
    	if($this->end_time){
    		$query->andFilterWhere(['<', 'create_time', strtotime("{$this->end_time} + 1 day " )]);
    		$where_sum .= " AND p.create_time < :end_time";//计和
    		$arr[':end_time'] = strtotime("{$this->end_time} + 1 day");
    	}
    	 
    	
    	if($this->create_time){
    	    $query->andFilterWhere(['=', 'pay_trade.create_time', strtotime($this->create_time)]);
    	}
    	if($this->id){
    	    $query->andFilterWhere(['=', 'pay_trade.id', $this->id]);
    	}
    	if($this->trade_no){
    	    $query->andFilterWhere(['=', 'pay_trade.trade_no', $this->trade_no]);
    	}
    	if($this->username){
    	    $query->andFilterWhere(['=', 'user_info.username', $this->username]);
    	}
    	if($this->price_state){
    	    if($this->price_state==12){
    	        $this->price_state=0;
    	    }
    	    $query->andFilterWhere(['=', 'price_state', $this->price_state]);
    	    if($this->price_state==0){
    	        $this->price_state=12;
    	    }
    	}
    	if($this->price_modify){
    	    $query->andFilterWhere(['=', 'price_modify', $this->price_modify]);
    	}
    	if($this->address){
    	    $query->andFilterWhere(['like', 'address', $this->address]);
    	}
    	 /*  if($this->operator_address){//运营商所在地区如 浙江杭州
    		$where_sum .= " AND p.address like :operator_address";//计和
    		$arr[':operator_address'] = $this->operator_address.'%';
    	} */
    	  
    	 /* if($this->operator_self_id){//运营商个人的单子
    	     $where_sum .=$acount_condition;
    	}   */
    	$dataProvider = new ActiveDataProvider([
    	    'query' => $query,
    	    'pagination'=>['pageSize' => '20'],
    	]);
    	$sql1 = "select sum(p.price_modify) as amount from pay_trade as p  where {$where_sum}  ";
    	$amount = PayTrade::findBySql($sql1,$arr)->one();
    	
    	$sql2 = "select sum(l.rmb) as pay_amount from pay_trade as p";
    			$sql2 .= " right join log_pay as l on p.trade_no = l.trade_no where l.state=2 and  {$where_sum}   ";
    	$pay_amount = PayTrade::findBySql($sql2,$arr)->one();
    	$query_amount['amount'] = $amount['amount'];
    	$query_amount['pay_amount'] = $pay_amount['pay_amount'];
    	
    	return array('dataProvider'=>$dataProvider,'amount'=>$query_amount);
    	
    }
    
    /**
     * 众筹销售报表
     * @param unknown $params
     * @return \yii\data\ActiveDataProvider[]|unknown[]
     */
    public function crowdreportSearch($params)
    {
        $this->load($params);
        //根据运营商地区来
        $user_type = Yii::$app->user->identity->user_type;
        $user_id=Yii::$app->user->identity->user_id;
        $condition=" pay_trade.trade_class=2   and pay_trade.price_state in (1,11) ";
        $where_sum = " WHERE p.trade_class=2 and p.price_state in (1,11)    ";//计和
        //运营商 看自己的订单
        if(intval($user_type)>1){
            $ifxgt=OperatorRelation::getifXat();//香港 澳门 台湾   直接in('台湾省','香港特别行政区','澳门特别行政区')
            $ifhascity=OperatorRelation::ifhascity();
            $ifhaszxsCountry=OperatorRelation::ifhaszxsCountry();
            $ifhascountry=OperatorRelation::ifhascountry();
            $ifhasprovince=OperatorRelation::ifhasprovince();
            if($ifhasprovince){
                $provinceStr=OperatorRelation::getProvinceNameById();
                $condition.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$provinceStr})";
                $where_sum.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$provinceStr})";
            }
            if($ifhascity){
                $cityStr=OperatorRelation::getCityNameByIdNprovincename();
                if($ifhasprovince){
                    $condition.=" or SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                    $where_sum.=" or SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                }else{
                    $condition.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                    $where_sum.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                }
            }
            if($ifhaszxsCountry||$ifhascountry){
                if($ifhasprovince||$ifhascity){
                    $a="or";
                }else{
                    $a="and";
                }
                $countryStr=OperatorRelation::getcountryNameById();
                if($ifhaszxsCountry){//有直辖市 也有区县 合并条件
                    if($ifhascountry){
                        $condition.=$a."  (SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) or SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr}))";
                        $where_sum.=$a."  (SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) or SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr}))";
                    }else{
                        $condition.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) ";
                        $where_sum.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) ";
                    }
                }else{
                    if($ifhascountry){
                        $condition.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr})";
                        $where_sum.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr})";
                    }
                }
            }
            //管理员 查看所有的  也可以查看某个运营商的
        }else{
            if($this->operator_self_id){
                $operation_id=intval($this->operator_self_id);
                if($operation_id>0){
                    $ifxgt=OperatorRelation::getifXatByOid($operation_id);//香港 澳门 台湾   直接in('台湾省','香港特别行政区','澳门特别行政区')
                    $ifhascity=OperatorRelation::ifhascityByOid($operation_id);
                    $ifhaszxsCountry=OperatorRelation::ifhaszxsCountryByOid($operation_id);
                    $ifhascountry=OperatorRelation::ifhascountryByOid($operation_id);
                    $ifhasprovince=OperatorRelation::ifhasprovinceByOid($operation_id);
                    if($ifhasprovince){
                        $provinceStr=OperatorRelation::getProvinceNameByOId($operation_id);
                        $condition.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$provinceStr})";
                        $where_sum.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$provinceStr})";
                    }
                    if($ifhascity){
                        $cityStr=OperatorRelation::getCityNameByOIdNprovincename($operation_id);
                        if($ifhasprovince){
                            $condition.=" or SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                            $where_sum.=" or SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                            
                        }else{
                            $condition.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                            $where_sum.=" and SUBSTR(SUBSTRING_INDEX(address,' ',2),CHAR_LENGTH(SUBSTRING_INDEX(address,' ',1))+1) in ({$cityStr})";
                            
                        }
                    }
                    if($ifhaszxsCountry||$ifhascountry){
                        if($ifhasprovince||$ifhascity){
                            $a="or";
                        }else{
                            $a="and";
                        }
                        $countryStr=OperatorRelation::getcountryNameByOId($operation_id);
                        if($ifhaszxsCountry){//有直辖市 也有区县 合并条件
                            if($ifhascountry){
                                $condition.=$a."  (SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) or SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr}))";
                                $where_sum.=$a."  (SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) or SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr}))";
                            }else{
                                $condition.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) ";
                                $where_sum.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',2))) in ({$countryStr}) ";
                            }
                        }else{
                            if($ifhascountry){
                                $condition.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr})";
                                $where_sum.=$a."  SUBSTR(address,1,CHAR_LENGTH(SUBSTRING_INDEX(address,' ',3))) in ({$countryStr})";
                            }
                        }
                    }
                }
            }
        }
        
        $query = PayTrade::find()->where($condition)->joinWith(['logPayInfo'])->joinWith(['userInfo']);
        $arr = array();
        if($this->start_time){
            $query->andFilterWhere(['>=', 'create_time', strtotime($this->start_time)]);
            $where_sum .= " AND p.create_time >= :start_time";//计和
            $arr[':start_time'] = strtotime($this->start_time);
        }
        
        if($this->end_time){
            $query->andFilterWhere(['<', 'create_time', strtotime("{$this->end_time} + 1 day " )]);
            $where_sum .= " AND p.create_time < :end_time";//计和
            $arr[':end_time'] = strtotime("{$this->end_time} + 1 day");
        }
        if($this->create_time){
            $query->andFilterWhere(['=', 'pay_trade.create_time', strtotime($this->create_time)]);
        }
        if($this->id){
            $query->andFilterWhere(['=', 'pay_trade.id', $this->id]);
        }
        if($this->trade_no){
            $query->andFilterWhere(['=', 'pay_trade.trade_no', $this->trade_no]);
        }
        if($this->username){
            $query->andFilterWhere(['=', 'user_info.username', $this->username]);
        }
        if($this->price_state){
            if($this->price_state==12){
                $this->price_state=0;
            }
            $query->andFilterWhere(['=', 'price_state', $this->price_state]);
            if($this->price_state==0){
                $this->price_state=12;
            }
        }
        if($this->price_modify){
            $query->andFilterWhere(['=', 'price_modify', $this->price_modify]);
        }
        if($this->address){
            $query->andFilterWhere(['like', 'address', $this->address]);
        }
        /*  if($this->operator_address){//运营商所在地区如 浙江杭州
         $where_sum .= " AND p.address like :operator_address";//计和
         $arr[':operator_address'] = $this->operator_address.'%';
         } */
        
        /* if($this->operator_self_id){//运营商个人的单子
         $where_sum .=$acount_condition;
         }   */
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);
        $sql1 = "select sum(p.price_modify) as amount from pay_trade as p {$where_sum}  ";
        $amount = PayTrade::findBySql($sql1,$arr)->one();
        
        $sql2 = "select sum(l.rmb) as pay_amount from pay_trade as p";
        $sql2 .= " right join log_pay as l on p.trade_no = l.trade_no {$where_sum}   ";
        $pay_amount = PayTrade::findBySql($sql2,$arr)->one();
        $query_amount['amount'] = $amount['amount'];
        $query_amount['pay_amount'] = $pay_amount['pay_amount'];
        return array('dataProvider'=>$dataProvider,'amount'=>$query_amount);
        
    }
    /**
     * 设计师订单
     */
    public function searchDesigner($params){
        $user_type = Yii::$app->user->identity->user_type;
        $user_id=Yii::$app->user->identity->user_id;
        $cityStr=OperatorRelation::getCityNameById($user_id);
        $this->load($params);
        $year=intval($this->year);
        if($year==0){
            $year=intval(date("Y"));
        }
        $createtime=strtotime(date("$year-01-01"));
        $end_time=strtotime(date("$year-12-31"));
        $condition=" pay_trade.trade_class=1 and user_info.user_type=3   and pay_trade.create_time>=:createtime and pay_trade.create_time<=:endtime
                    and (pay_trade.price_state=1 or pay_trade.price_state=11 ) ";
        $param=array(":createtime"=>$createtime,":endtime"=>$end_time);
        //如果是非管理员，则根据地址过滤
        if ($user_type != 1 ){//非后台管理员
            if(intval($user_id)>0){
                    $condition.=" and  SUBSTR(sys_user_designer.address,1,CHAR_LENGTH(SUBSTRING_INDEX(sys_user_designer.address,' ',3))) in ({$cityStr})";
                }
        }  
        $query = PayTrade::find()->joinWith(['userInfo','userInfo.sysUserDesigner'])
        ->select("user_info.id,pay_trade.user_id,pay_trade.audit_state,pay_trade.create_time,pay_trade.price_state,pay_trade.trade_state,
		sys_user_designer.name nickname,user_info.`username`,user_info.`tel`,sys_user_designer.address  user_area,user_info.user_type_class")
		->where($condition,$param)
		->groupBy("user_info.id") ;
		$dataProvider = new ActiveDataProvider([
		    'query' => $query,
		    'sort' => ['defaultOrder' => ['id'=>SORT_DESC]]
		]);
		if (!$this->validate()) {
		    // uncomment the following line if you do not want to return any records when validation fails
		    // $query->where('0=1');
		    return $dataProvider;
		}
		$query->andFilterWhere([
		    'user_info.id' => $this->id,
		]);
		$query->andFilterWhere(['like', 'user_info.username', $this->username])
		->andFilterWhere(['like', 'sys_user_designer.name', $this->nickname])
		->andFilterWhere(['like', 'user_info.tel', $this->tel])
		->andFilterWhere(['like', 'sys_user_designer.address', $this->user_area])
		;
		return $dataProvider;
    }
    /**
     * 装饰公司订单
     * @param unknown $params
     */
    public function searchComany($params){
    	//已经支付 部分已付  订单列表
    	
        $user_type = Yii::$app->user->identity->user_type;
        $user_id=Yii::$app->user->identity->user_id;
        $cityStr=OperatorRelation::getCityNameById($user_id);
        $this->load($params);
        $year=intval($this->year);
        if($year==0){
            $year=intval(date("Y"));
        }
        $createtime=strtotime(date("$year-01-01"));
        $end_time=strtotime(date("$year-12-31"));
        $condition=" pay_trade.trade_class=1 and user_info.user_type=4   and pay_trade.create_time>=:createtime and pay_trade.create_time<=:endtime
                    and (pay_trade.price_state=1 or pay_trade.price_state=11 ) ";
        $param=array(":createtime"=>$createtime,":endtime"=>$end_time);
        //如果是非管理员，则根据地址过滤
        if ($user_type != 1 ){//非后台管理员
            if(intval($user_id)>0){
                $condition.=" and  SUBSTR(sys_user_designer.address,1,CHAR_LENGTH(SUBSTRING_INDEX(sys_user_designer.address,' ',3))) in ({$cityStr})";
            }
        }  
        $query = PayTrade::find()->joinWith(['userInfo','userInfo.sysUserDesigner'])
        ->select("user_info.id,pay_trade.user_id,pay_trade.audit_state,pay_trade.create_time,pay_trade.price_state,pay_trade.trade_state,
		sys_user_designer.name nickname,user_info.`username`,user_info.`tel`,sys_user_designer.address  user_area,user_info.user_type_class")
		->where($condition,$param)
		->groupBy("user_info.id") ;
		$dataProvider = new ActiveDataProvider([
		    'query' => $query,
		    'sort' => ['defaultOrder' => ['id'=>SORT_DESC]]
		]);
		
		
		if (!$this->validate()) {
		    // uncomment the following line if you do not want to return any records when validation fails
		    // $query->where('0=1');
		    return $dataProvider;
		}
		$query->andFilterWhere([
		    'user_info.id' => $this->id,
		]);
		$query->andFilterWhere(['like', 'user_info.username', $this->username])
		->andFilterWhere(['like', 'sys_user_designer.name', $this->nickname])
		->andFilterWhere(['like', 'user_info.tel', $this->tel])
		->andFilterWhere(['like', 'sys_user_designer.address', $this->user_area])
		;
		
		return $dataProvider;
    }
    /**
     * 某个设计师 或者装饰公司的订单列表 以及使用了她优惠券的用户订单列表
     */
    public function searchDesignerView($user_id,$quarter,$year,$params){
     		$unixTime=$this->getUnixTimeByQuarty($year, $quarter);
    	 	//$query=PayTrade::find()->joinWith(['userInfo'])->where(" price_state in (1,11) and create_time>=".$begin_this_quarter." and create_time<=".$end_this_quarter."     and user_id=$user_id");//订单状态为已支付 或者已付部分的订单
	    	$sql="SELECT  pay_trade.*,user_info.`username`   FROM `pay_trade` 
			LEFT JOIN user_coupon ON   SUBSTRING(LEFT(SUBSTRING(content, LOCATE('\"coupon\":{\"all\":{\"id\":\"', content)), (LOCATE('\",', SUBSTRING(content, LOCATE('\"coupon\":{\"all\":{\"id\":\"', content))))
			),24,LENGTH(LEFT(SUBSTRING(content, LOCATE('\"coupon\":{\"all\":{\"id\":\"', content)), (LOCATE('\",', SUBSTRING(content, LOCATE('\"coupon\":{\"all\":{\"id\":\"', content))))
			))-24)=user_coupon.id	
			LEFT JOIN user_info ON user_info.id=pay_trade.`user_id` 
			WHERE pay_trade.trade_class=1 and price_state IN (1,11)  AND content  LIKE '%\"coupon\":{\"all\"%'  AND from_uid=:form_uid
	    	and pay_trade.create_time<=:end_time and pay_trade.create_time>=:begin_time
	    	OR (pay_trade.`user_id`=:form_uid  AND price_state IN (1,11)  and pay_trade.create_time<=:end_time and pay_trade.create_time>=:begin_time)
	    	";
	    	$param=array(":form_uid"=>$user_id,":begin_time"=>$unixTime['begin_time'],":end_time"=>$unixTime['end_time']);
	    	$query=PayTrade::findBySql($sql,$param);
	    	$dataProvider = new ActiveDataProvider([
	    			'query' => $query,
	    			'sort' => ['defaultOrder' => ['id'=>SORT_DESC]]
	    	]);
    	
    	$this->load($params);
    	if (!$this->validate()) {
    		return $dataProvider;
    	}
    	
    	$query->andFilterWhere([
    			'pay_trade.id' => $this->id,
    			'user_id' => $this->user_id,
    			'number' => $this->number,
    			'price' => $this->price,
    			'price_modify' => $this->price_modify,
    			'price_freight' => $this->price_freight,
    			'price_total' => $this->price_total,
    			'price_state' => $this->price_state,
    			//  'create_time' => $this->create_time,
    			'modify_time' => $this->modify_time,
    			'admin_uid' => $this->admin_uid,
    			'trade_state' => $this->trade_state,
    			'audit_state' => $this->audit_state,
    			'audit_uid' => $this->audit_uid,
    			'audit_time' => $this->audit_time,
    	]);
    	
    	$query->andFilterWhere(['like', 'pay_trade.trade_no', $this->trade_no])
    	->andFilterWhere(['like', 'address', $this->address])
    	->andFilterWhere(['like', 'message', $this->message])
    	->andFilterWhere(['like', 'price_m_memo', $this->price_m_memo])
    	->andFilterWhere(['like', 'content', $this->content])
    	->andFilterWhere(['like', 'user_info.username', $this->username])
    	->andFilterWhere(['like', 'FROM_UNIXTIME(create_time, "%Y-%m-%d")', $this->create_time])
    	;  
    	return $dataProvider;
    }
     
}
